<?php
namespace app\index\controller;
use think\Controller;
use think\Session;
use think\Request;
use think\View;
use think\Db;
use app\index\validate\Valid;
use PHPExcel;
use PHPExcel_Writer_Excel5;
use PHPExcel_Style_Alignment;
use PHPExcel_Style_Fill;
use PHPExcel_Style_Border;
//核查任务
class Hecha extends Common
{
	public function shanchu($id){
		$listids=Db::table("hecha")->alias("he")
		->join("hecha_list hl","hl.he_id=he.id")
		->where("he.id=$id")
		->field("hl.list_id")
		->select();
		$ids=[];

		for($i=0;$i<count($listids);$i++){
			$ids[]=$listids[$i]["list_id"];
		}
		$idsstt=implode(',',$ids);
		//return $idsstt;
		Db::startTrans();
		try{
			Db::table("cg_ti_list")->where("id in ($idsstt)")->update(["hc_stauts"=>0]);
			//return Db::table("cg_ti_list")->getLastSql();
			Db::table("hecha_list")->where("he_id=$id")->delete();
			Db::table("hecha")->where("id=$id")->delete();
		}catch(\Exception $e){
           	
	        	Db::rollBack();
	           return 2;
        	}
        	Db::commit();
        	return 1;

	}
	public function tijiao($id){
		$res=Db::table("hecha")->where("id=$id")->update(["state"=>1]);
		if($res)
			return 1;
		else
			return 2;
	}
	//下发列表
	public function lists($page=1,$list=10){
		$cuser=Session::get("employee_id");
		$where="he.cuser=$cuser ";

		$sum=Db::table("hecha")->alias("he")->where($where)->count();


    	$maxpage=ceil($sum/$list);
        $page=$page<$maxpage?$page:$maxpage;
        $page=$page>1?$page:1;

        $start=($page-1)*$list;


		$apply=Db::table("hecha")->alias("he")
		->join("employee e","he.cuser=e.id")
		->join("employee hee","he.heuser=hee.id")
		->where($where)->limit($start,$list)
		->field("he.id,he.no,e.em_name as tianbiaoren,hee.em_name as heren,he.ctime,he.state")
		->select();
		//print_r($apply);exit;
		for($i=0;$i<count($apply);$i++){
			//echo $i;
			$heid=$apply[$i]["id"];
			$tmp=Db::table("hecha_list")->alias("hl")
			->join("cg_ti_list apd","hl.list_id=apd.id")
			->join("cg_ti ap","apd.ti_id=ap.id")
			->where("hl.he_id=$heid")->group("ap.id")
			->field("ap.sn,ap.id,ap.type_id")->select();
			$apply[$i]["ti"]=$tmp;
			$apply[$i]["ctime"]=date("Y-m-d",$apply[$i]["ctime"]);
		}
		$this->assign("sum",$sum);
		$this->assign("page",$page);
		$this->assign("list",$list);
		$this->assign("maxpage",$maxpage);
		
		$this->assign("apply",$apply);
		return $this->fetch();

	}
	//新增或修改
	public function upadd(){
		$request = Request::instance();$data=$request->post();
		//print_r($data);
		if($data["hid"]){
			//修改
			Db::startTrans();
			try{
				$hedata["puser"]=Session::get("employee_id");$hedata["ptime"]=time();$hedata["heuser"]=$data["he_em"];
				$hedata["remark"]=$data["hecha"];$hedata["fujian"]=$data["attach"];
				$res1=Db::table("hecha")->where("id={$data['hid']}")->update($hedata);
				$listids=Db::table("hecha")->alias("he")
				->join("hecha_list hl","hl.he_id=he.id")
				->where("he.id={$data['hid']}")
				->field("hl.list_id")
				->select();
				$ids=[];

				for($i=0;$i<count($listids);$i++){
					$ids[]=$listids[$i]["list_id"];
				}
				$idsstt=implode(',',$ids);
				Db::table("cg_ti_list")->where("id in ($idsstt)")->update(["hc_stauts"=>0]);
				Db::table("hecha_list")->where("he_id={$data['hid']}")->delete();
				//插入从表
		      	$hefudata=[];$apdid=[];
		      	for($i=0;$i<count($data["apdid"]);$i++){
		      		$tmp=["no"=>$i+1,"he_id"=>$data["hid"],"list_id"=>$data["apdid"][$i],"dizhi"=>$data["dizhi"][$i]];
		      		$apdid[]=$data["apdid"][$i];
		      		$hefudata[]=$tmp;
		      	}
		      	$res2=Db::table("hecha_list")->insertAll($hefudata);
		      	$strapd=implode(',',$apdid);
		      	Db::table("cg_ti_list")->where("id in ($strapd)")->update(["hc_stauts"=>1]);
			}catch(\Exception $e){
           	
	        	Db::rollBack();
	           return json_encode(["msg"=>"系统繁忙请稍后再试","state"=>2]);
        	}

        	  if($res1&&$res2){
				Db::commit();
	            return json_encode(["msg"=>"","state"=>1]);
	        }else{
	        	Db::rollBack();
	           return json_encode(["msg"=>"系统繁忙请稍后再试","state"=>2]);
	        }
		}else{
			//新增
			Db::startTrans();
			try{
				//插入主表
				$hedata["cuser"]=Session::get("employee_id");$hedata["puser"]=Session::get("employee_id");
				$hedata["ctime"]=time();$hedata["ptime"]=time();$hedata["heuser"]=$data["he_em"];
				//生成单号
		    	$timenow=date("Ymd");
		        $num=Db::table("hecha")->where("no like '$timenow%'")->count();
		      	$num++;
		      	$strnum=$timenow.sprintf("%05d", $num);
		      	$hedata["no"]=$strnum;	$hedata["remark"]=$data["hecha"];$hedata["fujian"]=$data["attach"];
		      	$res1=Db::table("hecha")->insertGetId($hedata);
		      	//print_r($hedata);
		      	//插入从表
		      	$hefudata=[];$apdid=[];
		      	for($i=0;$i<count($data["apdid"]);$i++){
		      		$tmp=["no"=>$i+1,"he_id"=>$res1,"list_id"=>$data["apdid"][$i],"dizhi"=>$data["dizhi"][$i]];
		      		$apdid[]=$data["apdid"][$i];
		      		$hefudata[]=$tmp;
		      	}
		      	$res2=Db::table("hecha_list")->insertAll($hefudata);
		      	$strapd=implode(',',$apdid);
		      	Db::table("cg_ti_list")->where("id in ($strapd)")->update(["hc_stauts"=>1]);
			}catch(\Exception $e){
           	
	        	Db::rollBack();
	           return json_encode(["msg"=>"系统繁忙请稍后再试","state"=>2]);
        	}

        	  if($res1&&$res2){
				Db::commit();
	            return json_encode(["msg"=>"","state"=>1]);
	        }else{
	        	Db::rollBack();
	           return json_encode(["msg"=>"系统繁忙请稍后再试","state"=>2]);
	        }

		}
	}
	public function detail($hid){
		//修改
			$heapply=Db::table("hecha")->where("id=$hid")->find();
			if($heapply["state"]==0)
				$zhuangtai="暂存";
			else
				$zhuangtai="下发";
			$hename=Db::table("employee")->where("id={$heapply["heuser"]}")->find();
			$fujianid=$heapply["fujian"];
			if($fujianid)
				$fujian=Db::table("annex")->where("id in ($fujianid)")->select();
			else
				$fujian=[];
			$hechabeizhu=$heapply["remark"];
			$hechaday=date("Y-m-d H:i",$heapply["ctime"]);
			$eid=$heapply["cuser"];
			$user=Db::table("employee")->alias("e")
			->join("department_employee de","de.employee_id=e.id")
			->join("department d","de.department_id=d.id")
			->join("job_employee je","e.id=je.employee_id","LEFT")
			->join("job j","je.job_id=j.id","LEFT")
			->where("de.index=1 and e.id=$eid")
			->field("e.em_name,d.de_name,j.job_name")->find();
			$no=$heapply["no"];$heman=$heapply["heuser"];
			$hechalist=Db::table("hecha_list")->alias("hl")
			->join("cg_ti_list apd","hl.list_id=apd.id")
			->join("cg_ti ap","apd.ti_id=ap.id")
			->join("cg_jxs j","apd.jxs_id=j.id")
			->join("cg_market_zoning z","j.zoning=z.id")
			->join("cg_market_provice p","j.provice=p.id")
			->join("cg_market_city c","j.city=c.id")
			->join("employee e","ap.cuser=e.id")
			
			->join("cg_net n","apd.net_id=n.id","LEFT")
			->join("cg_paymethod pay","pay.id=apd.paymethod")
			->where("hl.he_id=$hid")
			->field("hl.list_id,ap.type_id,ap.sn as apno,apd.sn as apdno,ap.id as apid,z.title as fenqu,p.title as sheng,c.title as shi,e.em_name as zerenren,j.title as jxs,n.title as net,hl.dizhi,pay.title as pays,apd.pizhunweishui,apd.ptime,hl.tongzhi,hl.jieshu,hl.result")
			->select();
			for($i=0;$i<count($hechalist);$i++){
				$hechalist[$i]["ptime"]=date("Y-m-d",$hechalist[$i]["ptime"]);
			}
			$alluser=Db::table("users")->alias("u")
		->join("employee e","u.employee_id=e.id")->field("e.id,e.em_name")->select();
		$this->assign("hid",$hid);
		$this->assign("hechalist",$hechalist);
		$this->assign("fujian",$fujian);
		$this->assign("fujianid",$fujianid);
		$this->assign("hechabeizhu",$hechabeizhu);
		$this->assign("hechaday",$hechaday);
		$this->assign("alluser",$alluser);
		$this->assign("user",$user);
		$this->assign("no",$no);
		$this->assign("hename",$hename);
		$this->assign("heman",$heman);
		$this->assign("zhuangtai",$zhuangtai);
		return $this->fetch();
	}
	//下发页面
	public function xiafa($hid=null){
		if($hid){
			//修改
			$heapply=Db::table("hecha")->where("id=$hid")->find();
			$fujianid=$heapply["fujian"];
			if($fujianid)
				$fujian=Db::table("annex")->where("id in ($fujianid)")->select();
			else
				$fujian=[];
			$hechabeizhu=$heapply["remark"];
			$hechaday=date("Y-m-d H:i",$heapply["ctime"]);
			$eid=$heapply["cuser"];
			$user=Db::table("employee")->alias("e")
			->join("department_employee de","de.employee_id=e.id")
			->join("department d","de.department_id=d.id")
			->join("job_employee je","e.id=je.employee_id","LEFT")
			->join("job j","je.job_id=j.id","LEFT")
			->where("de.index=1 and e.id=$eid")
			->field("e.em_name,d.de_name,j.job_name")->find();
			$no=$heapply["no"];$heman=$heapply["heuser"];
			$hechalist=Db::table("hecha_list")->alias("hl")
			->join("cg_ti_list apd","hl.list_id=apd.id")
			->join("cg_ti ap","apd.ti_id=ap.id")
			->join("cg_jxs j","apd.jxs_id=j.id")
			->join("cg_market_zoning z","j.zoning=z.id")
			->join("cg_market_provice p","j.provice=p.id")
			->join("cg_market_city c","j.city=c.id")
			->join("employee e","ap.cuser=e.id")
			->join("cg_net n","apd.net_id=n.id","LEFT")
			->join("cg_paymethod pay","pay.id=apd.paymethod")
			->where("hl.he_id=$hid")
			->field("hl.list_id,ap.type_id,ap.sn as apno,apd.sn as apdno,ap.id as apid,z.title as fenqu,p.title as sheng,c.title as shi,e.em_name as zerenren,j.title as jxs,n.title as net,hl.dizhi,pay.title as pays,apd.pizhunweishui,apd.ptime")
			->select();
			for($i=0;$i<count($hechalist);$i++){
				$hechalist[$i]["ptime"]=date("Y-m-d",$hechalist[$i]["ptime"]);
			}
		}else{
			//新增
			$no="";
			//修改人
			$eid=Session::get("employee_id");
			$user=Db::table("employee")->alias("e")
			->join("department_employee de","de.employee_id=e.id")
			->join("department d","de.department_id=d.id")
			->join("job_employee je","e.id=je.employee_id","LEFT")
			->join("job j","je.job_id=j.id","LEFT")
			->where("de.index=1 and e.id=$eid")
			->field("e.em_name,d.de_name,j.job_name")->find();
			$hechaday=date("Y-m-d H:i");
			$hechabeizhu="";
			$fujian=[];
			$fujianid="";
			$heman=0;
			$hechalist=[];
		}
		$alluser=Db::table("users")->alias("u")
		->join("employee e","u.employee_id=e.id")->field("e.id,e.em_name")->select();
		$this->assign("hid",$hid);
		$this->assign("hechalist",$hechalist);
		$this->assign("fujian",$fujian);
		$this->assign("fujianid",$fujianid);
		$this->assign("hechabeizhu",$hechabeizhu);
		$this->assign("hechaday",$hechaday);
		$this->assign("alluser",$alluser);
		$this->assign("user",$user);
		$this->assign("no",$no);
		$this->assign("heman",$heman);
		return $this->fetch();
	}
	public function chazhao(){

		return $this->fetch();
	}
	//单号查询
	public function danhao($str){
		$arr=Db::table("cg_ti")->alias("ap")
		->join("cg_ti_list apd","apd.ti_id=ap.id")
		->join("cg_jxs j","apd.jxs_id=j.id")
		->join("cg_market_zoning z","j.zoning=z.id")
		->join("cg_market_provice p","j.provice=p.id")
		->join("cg_market_city c","j.city=c.id")
		->join("employee e","ap.cuser=e.id")
		->join("cg_net n","apd.net_id=n.id","LEFT")
		->join("cg_paymethod pay","pay.id=apd.paymethod")
		->where("ap.status=7 and ap.status2 is null and ap.sn='$str' and hc_stauts=0")
		->field("apd.id,ap.id as apid,ap.sn as apno,apd.sn as apdno,z.title as fenqu,p.title as sheng,c.title as shi,ap.type_id,e.em_name,j.title as jxs,n.title as net,n.detailAddress as dizhi,pay.title as pays,apd.pizhunweishui,apd.ptime")
		->select();
		for($i=0;$i<count($arr);$i++){
			$arr[$i]["ptime"]=date("Y-m-d",$arr[$i]["ptime"]);
			if($arr[$i]["type_id"]==0){
				$arr[$i]["type"]="非常规促销";
			}else if($arr[$i]["type_id"]==1){
				$arr[$i]["type"]="进场费用";
			}else{
				$arr[$i]["type"]="常规促销";
			}
			$arr[$i]["pizhunweishui"]=round($arr[$i]["pizhunweishui"],2);
			if(!$arr[$i]["dizhi"])
				$arr[$i]["dizhi"]="";
			if(!$arr[$i]["net"])
				$arr[$i]["net"]="";
			
		}
		return json_encode($arr);
	}
	//待核查
	public function daifalist(){
		$arr=Db::table("cg_ti")->alias("ap")
		->join("cg_ti_list apd","apd.ti_id=ap.id")
		->join("cg_jxs j","apd.jxs_id=j.id")
		->join("cg_market_zoning z","j.zoning=z.id")
		->join("cg_market_provice p","j.provice=p.id")
		->join("cg_market_city c","j.city=c.id")
		->join("employee e","ap.cuser=e.id")
		->join("cg_net n","apd.net_id=n.id","LEFT")
		->join("cg_paymethod pay","pay.id=apd.paymethod")
		->where("ap.status=7 and ap.status2 is null  and apd.hc_stauts=0 and apd.sfyqhc=1")
		->group("z.id")
		->field("z.title,z.id,count(apd.id) as nums")
		->select();
		//print_r($arr);
		$this->assign("arr",$arr);
		return $this->fetch();
	}
	//查询后下发
	public function chaxunxiafa($aid){
		$arr=Db::table("cg_ti")->alias("ap")
		->join("cg_ti_list apd","apd.ti_id=ap.id")
		->join("cg_jxs j","apd.jxs_id=j.id")
		->join("cg_market_zoning z","j.zoning=z.id")
		->join("cg_market_provice p","j.provice=p.id")
		->join("cg_market_city c","j.city=c.id")
		->join("employee e","ap.cuser=e.id")
		->join("cg_net n","apd.net_id=n.id","LEFT")
		->join("cg_paymethod pay","pay.id=apd.paymethod")
		->where("ap.status=7 and ap.status2 is null and apd.id=$aid and hc_stauts=0")
		->field("apd.id,ap.id as apid,ap.sn as apno,apd.sn as apdno,z.title as fenqu,p.title as sheng,c.title as shi,ap.type_id,e.em_name,j.title as jxs,n.title as net,n.detailAddress as dizhi,pay.title as pays,apd.pizhunweishui,apd.ptime")
		->select();
		for($i=0;$i<count($arr);$i++){
			$arr[$i]["ptime"]=date("Y-m-d",$arr[$i]["ptime"]);
			if($arr[$i]["type_id"]==0){
				$arr[$i]["type"]="非常规促销";
			}else if($arr[$i]["type_id"]==1){
				$arr[$i]["type"]="进场费用";
			}else{
				$arr[$i]["type"]="常规促销";
			}
			$arr[$i]["pizhunweishui"]=round($arr[$i]["pizhunweishui"],2);
			if(!$arr[$i]["dizhi"])
				$arr[$i]["dizhi"]="";
			if(!$arr[$i]["net"])
				$arr[$i]["net"]="";
			
		}
		return json_encode($arr);
	}
	//区域查询
	public function chazhaoxiafa($aid){
		$arr=Db::table("cg_ti")->alias("ap")
		->join("cg_ti_list apd","apd.ti_id=ap.id")
		->join("cg_jxs j","apd.jxs_id=j.id")
		->join("cg_market_zoning z","j.zoning=z.id")
		->join("cg_market_provice p","j.provice=p.id")
		->join("cg_market_city c","j.city=c.id")
		->join("employee e","ap.cuser=e.id")
		->join("cg_net n","apd.net_id=n.id","LEFT")
		->join("cg_paymethod pay","pay.id=apd.paymethod")
		->where("ap.status=7 and ap.status2 is null and z.id=$aid and apd.hc_stauts=0 and apd.sfyqhc=1")
		->field("apd.id,ap.id as apid,ap.sn as apno,apd.sn as apdno,z.title as fenqu,p.title as sheng,c.title as shi,ap.type_id,e.em_name,j.title as jxs,n.title as net,n.detailAddress as dizhi,pay.title as pays,apd.pizhunweishui,apd.ptime")
		->select();
		for($i=0;$i<count($arr);$i++){
			$arr[$i]["ptime"]=date("Y-m-d",$arr[$i]["ptime"]);
			if($arr[$i]["type_id"]==0){
				$arr[$i]["type"]="非常规促销";
			}else if($arr[$i]["type_id"]==1){
				$arr[$i]["type"]="进场费用";
			}else{
				$arr[$i]["type"]="常规促销";
			}
			$arr[$i]["pizhunweishui"]=round($arr[$i]["pizhunweishui"],2);
			if(!$arr[$i]["dizhi"])
				$arr[$i]["dizhi"]="";
			if(!$arr[$i]["net"])
				$arr[$i]["net"]="";
			
		}
		return json_encode($arr);
	}
	public function chaxunlist(){
		$request = Request::instance();$data=$request->post();
		//print_r($data);
		$where="z.id={$data["fenqu"]} and ap.status=7 and ap.status2 is null and hc_stauts=0";
		if($data["sheng"])
			$where.="and p.id={$data["sheng"]} ";
		if($data["shi"])
			$where.="and c.id={$data["shi"]} ";
		if($data["jxs"])
			$where.="and j.id={$data["jxs"]} ";
		if($data["system"])
			$where.="and s.id={$data["system"]} ";
		if($data["net"])
			$where.="and apd.net_id={$data["net"]} ";
		if($data["type"]!=-1)
			$where.="and ap.type_id={$data["type"]} ";
		if($data["start"]){
			$start=strtotime($data["start"]);
			$where.="and apd.start>=$start ";
		}
		if($data["end"]){
			$end=strtotime($data["end"]);
			$where.="and apd.end<=$end ";
		}
		$arr=Db::table("cg_ti")->alias("ap")
		->join("cg_ti_list apd","apd.ti_id=ap.id")
		->join("cg_jxs j","apd.jxs_id=j.id")
		->join("cg_market_zoning z","j.zoning=z.id")
		->join("cg_market_provice p","j.provice=p.id")
		->join("cg_market_city c","j.city=c.id")
		->join("employee e","ap.cuser=e.id")
		->join("cg_net n","apd.net_id=n.id","LEFT")
		->join("cg_system s","n.system_id=s.id","LEFT")
		->join("cg_paymethod pay","pay.id=apd.paymethod")
		->where($where)
		->field("apd.id,ap.id as apid,ap.sn as apno,apd.sn as apdno,z.title as fenqu,p.title as sheng,c.title as shi,ap.type_id,e.em_name,j.title as jxs,n.title as net,n.detailAddress as dizhi,pay.title as pays,apd.pizhunweishui,apd.ptime")
		->select();
		for($i=0;$i<count($arr);$i++){
			$arr[$i]["ptime"]=date("Y-m-d",$arr[$i]["ptime"]);
			if($arr[$i]["type_id"]==0){
				$arr[$i]["type"]="非常规促销";
			}else if($arr[$i]["type_id"]==1){
				$arr[$i]["type"]="进场费用";
			}else{
				$arr[$i]["type"]="常规促销";
			}
			$arr[$i]["pizhunweishui"]=round($arr[$i]["pizhunweishui"],2);
			if(!$arr[$i]["dizhi"])
				$arr[$i]["dizhi"]="";
			if(!$arr[$i]["net"])
				$arr[$i]["net"]="";
			
		}
		$this->assign("arr",$arr);
		return $this->fetch();
	}
	//详细查询
	public function chaxun(){
		$fenqu=Db::table("cg_market_zoning")->field("id,title")->select();
		$this->assign("fenqu",$fenqu);
		return $this->fetch();
	}
	public function qiusheng($id){
		$sheng=Db::table("cg_market_provice")->where("zoning=$id")->field("id,title")->select();
		return json_encode($sheng);
	}
	public function qiushi($id){
		$sheng=Db::table("cg_market_city")->where("provice=$id")->field("id,title")->select();
		return json_encode($sheng);
	}
	public function qiujxs($id){
		$sheng=Db::table("cg_jxs")->where("city=$id")->field("id,title")->select();
		return json_encode($sheng);
	}
	public function qiusys($id){
		$sheng=Db::table("cg_system")->where("jxs=$id")->field("id,title")->select();
		return json_encode($sheng);
	}
	public function qiunet($id){
		$sheng=Db::table("cg_net")->where("system_id=$id")->field("id,title")->select();
		return json_encode($sheng);
	}
	public function mytarsk($page=1,$list=10){
		$myid=Session::get("employee_id");
		$where="he.heuser=$myid and he.state>0";
		$sum=Db::table("hecha")->alias("he")->where($where)->count();

    	$maxpage=ceil($sum/$list);
        $page=$page<$maxpage?$page:$maxpage;
        $page=$page>1?$page:1;

        $start=($page-1)*$list;
        $where.=" and de.index=1";
		$detail=Db::table("hecha")->alias("he")
			->join("employee e","he.cuser=e.id")
			->join("department_employee de","de.employee_id=e.id")
			->join("department d","de.department_id=d.id")
			->join("employee ehe","he.heuser=ehe.id")
			->where($where)
			->limit($start,$list)
			->field("he.id,he.no,e.em_name as tianbiao,ehe.em_name as hejian,d.de_name,he.ctime,he.state")
			->select();
		for($i=0;$i<count($detail);$i++){
			$detail[$i]["ctime"]=date("Y-m-d",$detail[$i]["ctime"]);
			switch ($detail[$i]["state"]) {
				case 1:
					$detail[$i]["state"]="下发";
					break;
				case 2:
					$detail[$i]["state"]="完成";
					break;
				default:
					# code...
					break;
			}
		}
			$this->assign("sum",$sum);
		$this->assign("page",$page);
		$this->assign("list",$list);
		$this->assign("maxpage",$maxpage);
		
		$this->assign("detail",$detail);
		return $this->fetch();
	}
}